﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Catering_DBOpertion;
using System.Data.SqlClient;
using System.Data;

namespace Catering_Operation
{
    public class ChargePersonNumber
    {
        public ChargePersonNumber() { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("id", "ChargePersonNumber");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ChargePersonNumber");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public bool Exists(int classid, int year, int month)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM ChargePersonNumber");
            strSql.Append(" WHERE ClassID=@ClassID AND YEAR(Date)=@YDate AND MONTH(Date)=@MDate ");
            SqlParameter[] parameters = {
                    new SqlParameter("@ClassID", SqlDbType.Int,4),
                    new SqlParameter("@YDate", SqlDbType.Int,4),
                    new SqlParameter("@MDate", SqlDbType.Int,4)};
            parameters[0].Value = classid;
            parameters[1].Value = year;
            parameters[2].Value = month;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Catering_Model.ChargePersonNumber model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ChargePersonNumber(");
            strSql.Append("AreaID,ClassID,CompanyID,Date,Number)");
            strSql.Append(" values (");
            strSql.Append("@AreaID,@ClassID,@CompanyID,@Date,@Number)");
            SqlParameter[] parameters = {
					new SqlParameter("@AreaID", SqlDbType.Int,4),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@CompanyID", SqlDbType.Int,4),
					new SqlParameter("@Date", SqlDbType.DateTime,8),
					new SqlParameter("@Number", SqlDbType.Int,4)};
            parameters[0].Value = model.AreaID;
            parameters[1].Value = model.ClassID;
            parameters[2].Value = model.CompanyID;
            parameters[3].Value = model.Date;
            parameters[4].Value = model.Number;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(int number, int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ChargePersonNumber set ");
            strSql.Append("Number=@Number,");
            strSql.Append(" where id=@id ");

            SqlParameter[] parameters = {
					new SqlParameter("@Number", SqlDbType.Int,4),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = number;
            parameters[5].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(int number, int classid, int year, int month)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ChargePersonNumber set ");
            strSql.Append(" Number=@Number ");
            strSql.Append(" where ClassID=@ClassID AND YEAR(Date)=@YDate AND MONTH(Date)=@MDate ");

            SqlParameter[] parameters = {
                    new SqlParameter("@Number", SqlDbType.Int,4),                           
                    new SqlParameter("@ClassID", SqlDbType.Int,4),
                    new SqlParameter("@YDate", SqlDbType.Int,4),
                    new SqlParameter("@MDate", SqlDbType.Int,4)};
            parameters[0].Value = number;
            parameters[1].Value = classid;
            parameters[2].Value = year;
            parameters[3].Value = month;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ChargePersonNumber ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Catering_Model.ChargePersonNumber GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,AreaID,ClassID,CompanyID,Date,Number from ChargePersonNumber ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Catering_Model.ChargePersonNumber model = new Catering_Model.ChargePersonNumber();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AreaID"] != null && ds.Tables[0].Rows[0]["AreaID"].ToString() != "")
                {
                    model.AreaID = Convert.ToInt32(ds.Tables[0].Rows[0]["AreaID"]);
                }
                if (ds.Tables[0].Rows[0]["ClassID"] != null && ds.Tables[0].Rows[0]["ClassID"].ToString() != "")
                {
                    model.ClassID = Convert.ToInt32(ds.Tables[0].Rows[0]["ClassID"]);
                }
                if (ds.Tables[0].Rows[0]["CompanyID"] != null && ds.Tables[0].Rows[0]["CompanyID"].ToString() != "")
                {
                    model.CompanyID = Convert.ToInt32(ds.Tables[0].Rows[0]["CompanyID"]);
                }
                if (ds.Tables[0].Rows[0]["Date"] != null && ds.Tables[0].Rows[0]["Date"].ToString() != "")
                {
                    model.Date = Convert.ToDateTime(ds.Tables[0].Rows[0]["Date"]);
                }
                if (ds.Tables[0].Rows[0]["Number"] != null && ds.Tables[0].Rows[0]["Number"].ToString() != "")
                {
                    model.Number = Convert.ToInt32(ds.Tables[0].Rows[0]["Number"]);
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="classid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public Catering_Model.ChargePersonNumber GetModel(int classid, int year, int month)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP 1 ID,Date,Number,ClassID,AreaID,CompanyID FROM ChargePersonNumber ");
            strSql.Append(" WHERE ClassID=@ClassID AND YEAR(Date)=@YDate AND MONTH(Date)=@MDate ");
            SqlParameter[] parameters = {
                    new SqlParameter("@ClassID", SqlDbType.Int,4),
                    new SqlParameter("@YDate", SqlDbType.Int,4),
                    new SqlParameter("@MDate", SqlDbType.Int,4)};
            parameters[0].Value = classid;
            parameters[1].Value = year;
            parameters[2].Value = month;

            Catering_Model.ChargePersonNumber model = new Catering_Model.ChargePersonNumber();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AreaID"] != null && ds.Tables[0].Rows[0]["AreaID"].ToString() != "")
                {
                    model.AreaID = Convert.ToInt32(ds.Tables[0].Rows[0]["AreaID"]);
                }
                if (ds.Tables[0].Rows[0]["ClassID"] != null && ds.Tables[0].Rows[0]["ClassID"].ToString() != "")
                {
                    model.ClassID = Convert.ToInt32(ds.Tables[0].Rows[0]["ClassID"]);
                }
                if (ds.Tables[0].Rows[0]["CompanyID"] != null && ds.Tables[0].Rows[0]["CompanyID"].ToString() != "")
                {
                    model.CompanyID = Convert.ToInt32(ds.Tables[0].Rows[0]["CompanyID"]);
                }
                if (ds.Tables[0].Rows[0]["Date"] != null && ds.Tables[0].Rows[0]["Date"].ToString() != "")
                {
                    model.Date = Convert.ToDateTime(ds.Tables[0].Rows[0]["Date"]);
                }
                if (ds.Tables[0].Rows[0]["Number"] != null && ds.Tables[0].Rows[0]["Number"].ToString() != "")
                {
                    model.Number = Convert.ToInt32(ds.Tables[0].Rows[0]["Number"]);
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,AreaID,ClassID,CompanyID,Date,Number ");
            strSql.Append(" FROM ChargePersonNumber ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public int GetChargePersonNumberByClassIDAndDate(int classID, int year, int month)
        {
            string where = string.Format(" AND ClassID={0} AND year(Date)={1} AND month(Date)={2} ", classID, year, month);
            DataTable dt = GetList(where).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                if (!(dt.Rows[0]["Number"] is DBNull)) return Convert.ToInt32(dt.Rows[0]["Number"]);
            }
            return 0;
        }

        #endregion  Method
    }
}
